package cn.gwm.flink.streaming.strategy.multidimension;

import cn.gwm.flink.streaming.constant.ChargePredictFields;
import cn.gwm.flink.streaming.strategy.vehiclepredict.DwsStrConstant;
import cn.gwm.utils.HBaseDataSource;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @author : GW00280745
 * @date : 2022/12/26 10:38
 * @description :
 */
@Slf4j
public class PhoenixInfoDc implements Serializable {

    public JSONObject query(String condition) {
        //查一个曲线的开始点 一条曲线只需要查找开始点，之后根据开始点查找结束点，这样就是一条曲线
        Connection connection = null;
        try {
            String sql = selectStr.concat(condition);
            System.out.println(sql);
//            log.info(sql)
            connection = HBaseDataSource.ins().getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet rs = null;
            try {
                rs = preparedStatement.executeQuery();
            }catch (Exception e){
                //第一次如果报错了，那么就重试一次，如果还是错误先跳过
                rs = preparedStatement.executeQuery();
            }
            while (rs.next()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put(deviceIdKey,rs.getString(1));
                jsonObject.put(vinKey,rs.getString(1));
                jsonObject.put(itemTimeKey,rs.getString(2));
                jsonObject.put(tidKey,rs.getString(2));
                jsonObject.put(startChargeTimeKey,rs.getString(3));
                jsonObject.put(mapIdKey,rs.getString(4));
                jsonObject.put(currKey,rs.getString(5));
                jsonObject.put(innerSocKey,rs.getString(6));
                jsonObject.put(acAmbKey,rs.getString(7));
                jsonObject.put(modelCodeKey,rs.getString(8));
                jsonObject.put(endTimeKey,rs.getString(9));
                jsonObject.put(endSocKey,rs.getString(10));
                return jsonObject;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HBaseDataSource.ins().close(connection);
        }
        return null;
    }

    public JSONObject  queryMapId2(String condition,JSONObject line,Float innerSoc) {
        //查一个曲线的开始点 一条曲线只需要查找开始点，之后根据开始点查找结束点，这样就是一条曲线
        Connection connection = null;
        JSONObject res = null;
        try {
            Object mapid= line.get(mapIdKey);
            boolean b = mapid != null
                    && StringUtils.isNotBlank(mapid.toString())
                    && !StringUtils.equalsIgnoreCase(mapid.toString(), defaultStr);
            String sql = selectStr.concat(condition);
            if (StringUtils.startsWithIgnoreCase(condition, DwsStrConstant.ins().selectStr)){
                sql = condition;
            }
            System.out.println(sql);
//            log.info(sql)
            connection = HBaseDataSource.ins().getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            Float curr = line.getFloat(currKey);
            Float temp = line.getFloat(acAmbKey2);
            ResultSet rs = null;
            try {
                rs = preparedStatement.executeQuery();
            }catch (Exception e){
                //第一次如果报错了，那么就重试一次，如果还是错误先跳过
                rs = preparedStatement.executeQuery();
            }
            JSONObject firstJson = null;
            int cnt = 0 ;
            float comm = Float.MAX_VALUE ;
            JSONObject commJson = null;
            while (rs.next()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put(deviceIdKey,rs.getString(1));
                jsonObject.put(vinKey,rs.getString(1));
                jsonObject.put(itemTimeKey,rs.getString(2));
                jsonObject.put(tidKey,rs.getString(2));
                jsonObject.put(startChargeTimeKey,rs.getString(3));
                String mapIdVal = rs.getString(4);
                jsonObject.put(mapIdKey,mapIdVal);
                String currValStr = rs.getString(5);
                jsonObject.put(currKey,currValStr);
                String socValStr = rs.getString(6);
                jsonObject.put(innerSocKey,socValStr);
                String tempValStr = rs.getString(7);
                jsonObject.put(acAmbKey,tempValStr);
                jsonObject.put(modelCodeKey,rs.getString(8));
                jsonObject.put(endTimeKey,rs.getString(9));
                String endSocValStr = rs.getString(10);
                jsonObject.put(endSocKey,endSocValStr);
                jsonObject.put(steadyTimeKey,rs.getString(11));
                jsonObject.put(steadySocKey,rs.getString(12));
                jsonObject.put(steadyCurrKey,rs.getString(13));
                jsonObject.put(steadyTempminKey,rs.getString(14));
                if (b){
                    //如果当前记录的地点值是非空，那么找到第一个相等的记录
                    if (StringUtils.equalsIgnoreCase(mapid.toString(),mapIdVal)){
                        return jsonObject;
                    }
                }else {
                    //如果地点值是空，那么找到第一个记录
                    //如果地点是空，那么进行权重比较
//                    return jsonObject
                }
                if (cnt==0){
                    firstJson = jsonObject;
                }
                cnt++;
                float w=weight(curr,currValStr,temp,tempValStr,innerSoc,socValStr,endSocValStr);
                if (w<comm){
                    comm = w;
                    commJson = jsonObject;
                }
            }
            if (b){
                //如果当前记录的地点非空，但是没有找到匹配的数据只能找第一个
                return firstJson;
            }else{
                //但当电流温度soc有了权重且没有地点那么就取一个权重最小的
                if (comm != Float.MAX_VALUE){
                    return commJson;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HBaseDataSource.ins().close(connection);
        }
        return null;
    }

    private float weight(Float curr, String currValStr, Float temp, String tempValStr, Float innerSoc, String socValStr,String endSocValStr) {
        try {
            float deltaCurr = Math.abs(Math.abs(Float.parseFloat(currValStr)) - Math.abs(curr));
            float deltaTemp = Math.abs(Math.abs(Float.parseFloat(tempValStr)) - Math.abs(temp));
            float deltaSoc = Math.abs(Math.abs(Float.parseFloat(socValStr)) - Math.abs(innerSoc));
            float deltaEndSoc = Math.abs(Float.parseFloat(endSocValStr) - 95f);
            float v = deltaCurr + deltaTemp + deltaSoc + deltaEndSoc;
            return v;
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0f;
    }

    public JSONObject queryMapId(String condition,JSONObject line) {
        //查一个曲线的开始点 一条曲线只需要查找开始点，之后根据开始点查找结束点，这样就是一条曲线
        Connection connection = null;
        JSONObject res = null;
        try {
            Object mapid= line.get(mapIdKey);
            boolean b = mapid != null
                    && StringUtils.isNotBlank(mapid.toString())
                    && !StringUtils.equalsIgnoreCase(mapid.toString(), defaultStr);
            String sql = selectStr.concat(condition);
            System.out.println(sql);
//            log.info(sql)
            connection = HBaseDataSource.ins().getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet rs = null;
            try {
                rs = preparedStatement.executeQuery();
            }catch (Exception e){
                //第一次如果报错了，那么就重试一次，如果还是错误先跳过
                rs = preparedStatement.executeQuery();
            }
            JSONObject firstJson = null;
            int cnt = 0 ;
            while (rs.next()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put(deviceIdKey,rs.getString(1));
                jsonObject.put(vinKey,rs.getString(1));
                jsonObject.put(itemTimeKey,rs.getString(2));
                jsonObject.put(tidKey,rs.getString(2));
                jsonObject.put(startChargeTimeKey,rs.getString(3));
                String mapIdVal = rs.getString(4);
                jsonObject.put(mapIdKey,mapIdVal);
                jsonObject.put(currKey,rs.getString(5));
                jsonObject.put(innerSocKey,rs.getString(6));
                jsonObject.put(acAmbKey,rs.getString(7));
                jsonObject.put(modelCodeKey,rs.getString(8));
                jsonObject.put(endTimeKey,rs.getString(9));
                jsonObject.put(endSocKey,rs.getString(10));
                jsonObject.put(steadyTimeKey,rs.getString(11));
                jsonObject.put(steadySocKey,rs.getString(12));
                jsonObject.put(steadyCurrKey,rs.getString(13));
                jsonObject.put(steadyTempminKey,rs.getString(14));
                if (b){
                    //如果地点值是非空，那么找到第一个相等的记录
                    if (StringUtils.equalsIgnoreCase(mapid.toString(),mapIdVal)){
                        return jsonObject;
                    }
                }else {
                    //如果地点值是空，那么找到第一个记录
                    return jsonObject;
                }
                if (cnt==0){
                    firstJson = jsonObject;
                }
                cnt++;
            }
            if (b){return firstJson;}
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HBaseDataSource.ins().close(connection);
        }
        return null;
    }

    DimensionOrder dimension = new DimensionOrder();
    private String selectStr = "select \"deviceid\",\"item_time\",\"startchargetime\",\"aoiid\",\"bms_battcurr_rm\",\"bms_innersoc\",\"bms_rmc_moduletempmin\",\"model_code\",\"endchargetime\",\"end_bms_soc\",\"steady_time\",\"steady_soc\",\"steady_curr\",\"steady_tempmin\" from \"vaas_dws\".\"dws_multidimension_charge_dcobc\" ";
    private String selectStrV1 = "select \"deviceid\",\"item_time\",\"startchargetime\",round((to_number(\"endchargetime\") - to_number(\"item_time\"))/(60*1000),1) as d,round((to_number(\"end_bms_soc\") - to_number(\"bms_innersoc\")),1) as c,round(( round((to_number(\"endchargetime\") - to_number(\"item_time\"))/(60.0*1000),1) ) / ( round((to_number(\"end_bms_soc\") - to_number(\"bms_innersoc\")),1) ),1) as e,\"aoiid\",\"bms_battcurr_rm\",\"bms_innersoc\",\"bms_rmc_moduletempmin\",\"model_code\",\"endchargetime\",\"end_bms_soc\",\"steady_time\",\"steady_soc\",\"steady_curr\",\"steady_tempmin\" from \"vaas_dws\".\"dws_multidimension_charge_dcobc\" ";
    private String selectStrV2 = "select \"deviceid\",\"item_time\",\"startchargetime\",round((to_number(\"endchargetime\") - to_number(\"item_time\"))/(60*1000),1) as d,round((to_number(\"end_bms_soc\") - to_number(\"bms_innersoc\")),1) as c,round(( round((to_number(\"endchargetime\") - to_number(\"item_time\"))/(60.0*1000),1) ) / ( round((to_number(\"end_bms_soc\") - to_number(\"bms_innersoc\")),1) ),1) as e,\"aoiid\",\"bms_battcurr_rm\",\"bms_innersoc\",\"bms_rmc_moduletempmin\",\"model_code\",\"endchargetime\",\"end_bms_soc\",\"steady_time\",\"steady_soc\",\"steady_curr\",\"steady_tempmin\",\"poiid\" from \"vaas_dws\".\"dws_multidimension_charge_dcobc\" ";
    private String selectStrV3 = " select substr(id,0,17) as \"deviceid\",reverse(substr(id,19,32)) as \"item_time\",\"bms_innersoc\",\"startchargetime\",\"bms_rmc_moduletempmin\",\"e\",\"model_code\",\"bms_battcurr_rm\",\"poiid\",\"aoiid\" from \"vaas_dws\".\"dws_multidimension_charge_dcobc_v2\" ";
    private String innerSocKey = "bms_innersoc";
    private String modelCodeKey = "model_code";
    private String startChargeTimeKey = "startChargeTime";
    private String vinKey = "vin";
    private String deviceIdKey = "deviceId";
    private String tidKey = "tid";
    private String itemTimeKey = "item_time";
    private String mapIdKey = "mapId";
    private String poiIdKey = "poiid";
    private String aoiIdKey = "aoiid";
    private String currKey = "bms_batt_curr_rm";
    private String inletTempKey = "bms_inlettemps";
    private String heatReqKey = "bms_heatreq";
    private String acOpenKey = "acopensts";
    private String socLimKey = "tbox_batt_soc_lim";
    private String acAmbKey = "bms_rmc_moduletempmin";
    private String acAmbKey2 = "bms_rmc_module_temp_min";
    private String sohKey = "soh_cor";
    private String endTimeKey = "endchargetime";
    private String endSocKey = "end_bms_soc";
    private String steadyTimeKey = "steady_time";
    private String steadySocKey = "steady_soc";
    private String steadyCurrKey = "steady_curr";
    private String steadyTempminKey = "steady_tempmin";
    private String whereStr = " where ";

    public String startStageQuery(JSONObject line){
        String vin= line.getString(vinKey);
        String startChargeTime = line.getString(ChargePredictFields.ins().srcFieldStartChargeTime);
        Object mapid= line.get(mapIdKey);
        Object bmsBattcurr= line.get(currKey);
        Object acambtemp= line.get(acAmbKey);
        if (acambtemp==null){
            acambtemp = line.get(acAmbKey2);
        }
        //车辆vin、时间、地点、电流、温度
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(whereStr);
        dimension.deviceIdDim(stringBuilder,vin,true);
        dimension.andOps(stringBuilder);
        boolean firstTime = line.getBooleanValue(ChargePredictFields.ins().firstTimeField);
        dimension.startTimeDim(stringBuilder,startChargeTime,firstTime);
        dimension.andOps(stringBuilder);
        dimension.currDim(stringBuilder,bmsBattcurr,false,10);
        dimension.andOps(stringBuilder);
        dimension.tempDim(stringBuilder,acambtemp,false);
//        dimension.andOps(stringBuilder)
//        dimension.startStageSoc(stringBuilder)
        dimension.orderBy1(stringBuilder,acambtemp,bmsBattcurr);
        return stringBuilder.toString();
    }
    private String a="select \"deviceid\",\"item_time\",\"startchargetime\",\"mapid\",\"bms_battcurr_rm\",\"bms_innersoc\",\"bms_rmc_moduletempmin\",\"model_code\",\"endchargetime\",\"end_bms_soc\",\"steady_time\",\"steady_soc\",\"steady_curr\",\"steady_tempmin\" from (select \"deviceid\",\"item_time\",\"startchargetime\",\"mapid\",\"bms_battcurr_rm\",\"bms_innersoc\",\"bms_rmc_moduletempmin\",\"model_code\",\"endchargetime\",\"end_bms_soc\",\"steady_time\",\"steady_soc\",\"steady_curr\",\"steady_tempmin\",(abs(to_number(\"bms_innersoc\"))-abs(%s)) as \"delta_soc\",(abs(to_number(\"bms_battcurr_rm\"))-abs(%s)) as \"delta_curr\",(to_number(\"bms_rmc_moduletempmin\") %s) as \"delta_temp\" from \"vaas_dws\".\"dws_multidimension_charge\" %s)a0 where \"delta_soc\"<=2 and abs(\"delta_curr\")<=2  order by \"delta_soc\"+\"delta_curr\" limit 1";
    public String startQuery(JSONObject line, Float bmsSoc,Object steadyCurr,Object steadyTempmin){
        Object vin= line.get(vinKey);
        Long tidVal = line.getLong(tidKey);
        Object mapid= line.get(mapIdKey);
        Object bmsBattcurr= line.get(currKey);
        Object bmsInlettemps= line.get(inletTempKey);
        Object bmsHeatreq= line.get(heatReqKey);
        Object acopensts= line.get(acOpenKey);
        Object tboxBattSocLim= line.get(socLimKey);
        Object acambtemp= line.get(acAmbKey);
        if (acambtemp==null){
            acambtemp = line.get(acAmbKey2);
        }
        Object soh= line.get(sohKey);
        String startChargeTime = line.getString(ChargePredictFields.ins().srcFieldStartChargeTime);
        //车辆vin、时间、地点、电流、起点soc、温度、目标soc
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(whereStr);
        dimension.chrgNumDim(stringBuilder);
        dimension.andOps(stringBuilder);
        dimension.deviceIdDim(stringBuilder,vin,true);
        dimension.andOps(stringBuilder);
        boolean firstTime = line.getBooleanValue(ChargePredictFields.ins().firstTimeField);
        dimension.startTimeDim(stringBuilder,startChargeTime,firstTime);
        dimension.andOps(stringBuilder);
        dimension.currDim(stringBuilder,bmsBattcurr,false,10);
        dimension.andOps(stringBuilder);
        dimension.socDim2(stringBuilder,bmsSoc,5);
        dimension.andOps(stringBuilder);
        dimension.tempDim(stringBuilder,acambtemp,false);
        Object temp = null;
        if (StringUtils.containsIgnoreCase(acambtemp.toString(),DwsStrConstant.ins().fuStr)){
            temp = acambtemp;
        }else {
            temp = "-".concat(acambtemp.toString());
        }
        String format = String.format(a, bmsSoc,bmsBattcurr, temp, stringBuilder);
//        dimension.andOps(stringBuilder)
//        dimension.endSocDim(stringBuilder)
        dimension.orderBy2(stringBuilder,acambtemp,bmsBattcurr,bmsSoc);
        //拼接
        return stringBuilder.toString();
//        return format;
    }
    public String startObcCurr(JSONObject line, Float bmsSoc, boolean endSocFlag,boolean isObc,int currStepVal,boolean mapIdBool,boolean breakBool){
        Object vin= line.get(vinKey);
        Long tidVal = line.getLong(tidKey);
        Object mapid= line.get(mapIdKey);
        Object bmsBattcurr= line.get(currKey);
        Object bmsInlettemps= line.get(inletTempKey);
        Object bmsHeatreq= line.get(heatReqKey);
        Object acopensts= line.get(acOpenKey);
        Object tboxBattSocLim= line.get(socLimKey);
        Object acambtemp= line.get(acAmbKey);
        if (acambtemp==null){
            acambtemp = line.get(acAmbKey2);
        }
        Object soh= line.get(sohKey);
        String startChargeTime = line.getString(ChargePredictFields.ins().srcFieldStartChargeTime);
        //车辆vin、地点、电流、起点soc、电池温度、目标soc、加热、空调开启
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(whereStr);
//        if (
//                mapid  null
//                        && StringUtils.isNotBlank(mapid.toString())
//                        && !StringUtils.equalsIgnoreCase(mapid.toString(),defaultStr)
//        ){
//            if (mapIdBool){
//                dimension.mapIdDim(stringBuilder,mapid.toString())
//                dimension.andOps(stringBuilder)
//            }
//        }
        dimension.deviceIdDim(stringBuilder,vin,true);
        dimension.andOps(stringBuilder);
        boolean firstTime = line.getBooleanValue(ChargePredictFields.ins().firstTimeField);
        dimension.startTimeDim(stringBuilder,startChargeTime,firstTime);
        dimension.andOps(stringBuilder);
        dimension.currDim(stringBuilder,bmsBattcurr,isObc,currStepVal);
        dimension.andOps(stringBuilder);
        dimension.socDim(stringBuilder,bmsSoc,endSocFlag);
        dimension.andOps(stringBuilder);
        dimension.tempDim(stringBuilder,acambtemp,breakBool);
        if (breakBool){
            dimension.andOps(stringBuilder);
            dimension.endSocDim(stringBuilder);
        }
        dimension.orderBy(stringBuilder,acambtemp,bmsBattcurr,bmsSoc);
        //拼接
        return stringBuilder.toString();
    }
    private String whereQuotation = "\'";
    private String whereAnd = " and ";
    private String whereDeviceId = " \"deviceid\"=\'";
    private String whereStartChargeTime = " \"startchargetime\"=\'";
    private String whereTimeOps = " and \"item_time\">=\"startchargetime\" ";
    private String defaultStr = "-999999";

    public String startObcCurrVehicle(JSONObject line, Float bmsSoc, boolean endSocFlag,boolean mapIdBool){
        Object vin= line.get(vinKey);
        Long tidVal = line.getLong(tidKey);
        Object mapid= line.get(mapIdKey);
        Object bmsBattcurr= line.get(currKey);
        Object bmsInlettemps= line.get(inletTempKey);
        Object bmsHeatreq= line.get(heatReqKey);
        Object acopensts= line.get(acOpenKey);
        Object tboxBattSocLim= line.get(socLimKey);
        Object acambtemp= line.get(acAmbKey);
        Object soh= line.get(sohKey);
        //车辆vin、地点、电流、起点soc、电池温度、目标soc、加热、空调开启
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(whereStr);
//        if (
//                mapid  null
//                        && StringUtils.isNotBlank(mapid.toString())
//                        && !StringUtils.equalsIgnoreCase(mapid.toString(),defaultStr)
//        ){
//            if (mapIdBool){
//                dimension.mapIdDim(stringBuilder,mapid.toString())
//                dimension.andOps(stringBuilder)
//            }
//        }
        //相似车型目前没有对应的modelcode
//        dimension.deviceIdDimLg(stringBuilder,vin)
//        dimension.andOps(stringBuilder)

        dimension.itemTimeDim(stringBuilder,tidVal);
        dimension.andOps(stringBuilder);
        dimension.currDim(stringBuilder,bmsBattcurr,false,5);
        dimension.andOps(stringBuilder);
        dimension.socDim(stringBuilder,bmsSoc,endSocFlag);
        dimension.andOps(stringBuilder);
        dimension.tempDim(stringBuilder,acambtemp,false);
        dimension.andOps(stringBuilder);
        dimension.endSocDim(stringBuilder);
        dimension.orderBy(stringBuilder,acambtemp,bmsBattcurr,bmsSoc);
        //拼接
        return stringBuilder.toString();
    }

    public String startObcTemp(JSONObject line, Float bmsSoc, boolean endSocFlag,boolean isObc,int currStepVal,boolean mapIdBool,boolean breakBool){
        Object vin= line.get(vinKey);
        Long tidVal = line.getLong(tidKey);
        Object mapid= line.get(mapIdKey);
        Object bmsBattcurr= line.get(currKey);
        Object bmsInlettemps= line.get(inletTempKey);
        Object bmsHeatreq= line.get(heatReqKey);
        Object acopensts= line.get(acOpenKey);
        Object tboxBattSocLim= line.get(socLimKey);
        Object acambtemp= line.get(acAmbKey);
        if (acambtemp==null){
            acambtemp=line.get(acAmbKey2);
        }
        Object soh= line.get(sohKey);
        String startChargeTime = line.getString(ChargePredictFields.ins().srcFieldStartChargeTime);
        //车辆vin、地点、电流、起点soc、电池温度、目标soc、加热、空调开启
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(whereStr);
//        if (
//                mapid  null
//                        && StringUtils.isNotBlank(mapid.toString())
//                        && !StringUtils.equalsIgnoreCase(mapid.toString(),defaultStr)
//        ){
//            if (mapIdBool){
//                dimension.mapIdDim(stringBuilder,mapid.toString())
//                dimension.andOps(stringBuilder)
//            }
//        }
        dimension.deviceIdDim(stringBuilder,vin,true);
        dimension.andOps(stringBuilder);
        boolean firstTime = line.getBooleanValue(ChargePredictFields.ins().firstTimeField);
        dimension.startTimeDim(stringBuilder,startChargeTime,firstTime);
        dimension.andOps(stringBuilder);
        dimension.currDim(stringBuilder,bmsBattcurr,isObc,currStepVal);
        dimension.andOps(stringBuilder);
        dimension.socDim(stringBuilder,bmsSoc,endSocFlag);
//        dimension.andOps(stringBuilder)
//        dimension.tempDim(stringBuilder,acambtemp,breakBool)
        if (breakBool){
            dimension.andOps(stringBuilder);
            dimension.endSocDim(stringBuilder);
        }
        dimension.orderBy(stringBuilder,acambtemp,bmsBattcurr,bmsSoc);
        //拼接
        return stringBuilder.toString();
    }
    public String endSoc(JSONObject line){
        Object vin= line.get(vinKey);
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(whereStr);
        stringBuilder.append(whereDeviceId).append(vin).append(whereQuotation);
        long startChargeTime = line.getLongValue(startChargeTimeKey);
        stringBuilder.append(whereAnd).append(whereStartChargeTime).append(startChargeTime).append(whereQuotation);
        stringBuilder.append(whereTimeOps);
        dimension.orderByEndSoc(stringBuilder);
        return stringBuilder.toString();
    }
    public String startQueryV1(JSONObject line, Float bmsSoc,Object steadyCurr,Object steadyTempmin){
        Object vin= line.get(vinKey);
        Long tidVal = line.getLong(tidKey);
        Object mapid= line.get(mapIdKey);
        Object bmsBattcurr= line.get(currKey);
        Object bmsInlettemps= line.get(inletTempKey);
        Object bmsHeatreq= line.get(heatReqKey);
        Object acopensts= line.get(acOpenKey);
        Object tboxBattSocLim= line.get(socLimKey);
        Object acambtemp= line.get(acAmbKey);
        if (acambtemp==null){
            acambtemp = line.get(acAmbKey2);
        }
        Object soh= line.get(sohKey);
        String startChargeTime = line.getString(ChargePredictFields.ins().srcFieldStartChargeTime);
        //车辆vin、时间、地点、电流、起点soc、温度、目标soc
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(whereStr);
        dimension.dcDim(stringBuilder);
        dimension.andOps(stringBuilder);
        dimension.deviceIdDim(stringBuilder,vin,true);
        dimension.andOps(stringBuilder);
        boolean firstTime = line.getBooleanValue(ChargePredictFields.ins().firstTimeField);
        dimension.startTimeDim(stringBuilder,startChargeTime,firstTime);
        dimension.andOps(stringBuilder);
        dimension.currDimDc(stringBuilder,bmsBattcurr,false,10);
        dimension.andOps(stringBuilder);
        dimension.socDim2(stringBuilder,bmsSoc,5);
//        dimension.andOps(stringBuilder)
//        dimension.tempDim(stringBuilder,acambtemp,false)
        dimension.andOps(stringBuilder);
        dimension.testS(stringBuilder);
        dimension.orderByDc2(stringBuilder,acambtemp,bmsBattcurr);
        //拼接
        return stringBuilder.toString();
//        return format
    }

    public String startDcQueryV2(JSONObject line, Float bmsSoc, Object steadyCurr, Object steadyTempmin){
        Object vin= line.get(vinKey);
        Long tidVal = line.getLong(tidKey);
        Object mapid= line.get(mapIdKey);
        Object bmsBattcurr= line.get(currKey);
        Object bmsInlettemps= line.get(inletTempKey);
        Object bmsHeatreq= line.get(heatReqKey);
        Object acopensts= line.get(acOpenKey);
        Object tboxBattSocLim= line.get(socLimKey);
        Object acambtemp= line.get(acAmbKey);
        if (acambtemp==null){
            acambtemp = line.get(acAmbKey2);
        }
        Object soh= line.get(sohKey);
        String startChargeTime = line.getString(ChargePredictFields.ins().srcFieldStartChargeTime);
        //车辆vin、时间、地点、电流、起点soc、温度、目标soc
        StringBuilder stringBuilder = new StringBuilder();
        // where "bms_innersoc">='68.0' and "bms_innersoc"<='72.0' and "bms_rmc_moduletempmin"<='05' and "bms_rmc_moduletempmin">='00' and "startchargetime"<'1674703195000' order by "startchargetime" desc ;
        stringBuilder.append(whereStr);
        dimension.socDim3(stringBuilder,bmsSoc,2);
        dimension.andOps(stringBuilder);
        dimension.tempDim2(stringBuilder,acambtemp,false);
        dimension.andOps(stringBuilder);
        boolean firstTime = line.getBooleanValue(ChargePredictFields.ins().firstTimeField);
        dimension.startTimeDim(stringBuilder,startChargeTime,firstTime);
        dimension.orderByDc2(stringBuilder,acambtemp,bmsBattcurr);
        //拼接
        return stringBuilder.toString();
//        return format;
    }
    public Float queryMapId3(String condition,JSONObject line,float innerSoc) {
        //查一个曲线的开始点 一条曲线只需要查找开始点，之后根据开始点查找结束点，这样就是一条曲线
        Connection connection = null;
        JSONObject res = null;
        try {
            Object mapid= line.get(mapIdKey);
            boolean b = mapid != null
                    && StringUtils.isNotBlank(mapid.toString())
                    && !StringUtils.equalsIgnoreCase(mapid.toString(), defaultStr);
            String sql = selectStrV1.concat(condition);
            if (StringUtils.startsWithIgnoreCase(condition,DwsStrConstant.ins().selectStr)){
                sql = condition;
            }
            System.out.println(sql);
//            log.info(sql)
            connection = HBaseDataSource.ins().getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            Float curr = line.getFloat(currKey);
            Float temp = line.getFloat(acAmbKey2);
            ResultSet rs = null;
            try {
                rs = preparedStatement.executeQuery();
            }catch (Exception e){
                //第一次如果报错了，那么就重试一次，如果还是错误先跳过
                rs = preparedStatement.executeQuery();
            }
            JSONObject firstJson = new JSONObject();
            int cnt = 0 ;
            float total = 0 ;

            while (rs.next()) {
                float timeVal = rs.getFloat(4);
                total += timeVal;
                cnt++;
            }
            if (cnt==0){
                return null;
            }
            float v = total / cnt;
            return v;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HBaseDataSource.ins().close(connection);
        }
        return null;
    }
    public Float queryMapId4(String condition,JSONObject line,float innerSoc) {
        //查一个曲线的开始点 一条曲线只需要查找开始点，之后根据开始点查找结束点，这样就是一条曲线
        Connection connection = null;
        JSONObject res = null;
        try {
            Object mapid= line.get(mapIdKey);
            boolean b = mapid != null
                    && StringUtils.isNotBlank(mapid.toString())
                    && !StringUtils.equalsIgnoreCase(mapid.toString(), defaultStr);
            String sql = selectStrV1.concat(condition);
            if (StringUtils.startsWithIgnoreCase(condition,DwsStrConstant.ins().selectStr)){
                sql = condition;
            }
            System.out.println(sql);
//            log.info(sql)
            connection = HBaseDataSource.ins().getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            Float curr = line.getFloat(currKey);
            Float temp = line.getFloat(acAmbKey2);
            ResultSet rs = null;
            try {
                rs = preparedStatement.executeQuery();
            }catch (Exception e){
                //第一次如果报错了，那么就重试一次，如果还是错误先跳过
                rs = preparedStatement.executeQuery();
            }
            JSONObject firstJson = new JSONObject();
            int cnt = 0 ;
            float total = 0 ;

            while (rs.next()) {
                float timeVal = rs.getFloat(4);
//                total += timeVal
                float deltaSoc = rs.getFloat(5);
                if (deltaSoc<=0){continue;}
                float avgTime = rs.getFloat(6);
                total += avgTime;
                cnt++;
            }
            if (cnt==0){
                return null;
            }
            float v = total / cnt;
            return v;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HBaseDataSource.ins().close(connection);
        }
        return null;
    }

    public Float queryMapId5(String condition,JSONObject line,float innerSoc) {
        //查一个曲线的开始点 一条曲线只需要查找开始点，之后根据开始点查找结束点，这样就是一条曲线
        Connection connection = null;
        try {
            String sql = selectStrV2.concat(condition);
            if (StringUtils.startsWithIgnoreCase(condition,DwsStrConstant.ins().selectStr)){
                sql = condition;
            }
            System.out.println(sql);
//            log.info(sql)
            connection = HBaseDataSource.ins().getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            Float curr = line.getFloat(currKey);
            Float temp = line.getFloat(acAmbKey2);
            String aoiidLine= line.getString(aoiIdKey);
            String poiidLine= line.getString(poiIdKey);

            ResultSet rs = null;
            try {
                rs = preparedStatement.executeQuery();
            }catch (Exception e){
                //第一次如果报错了，那么就重试一次，如果还是错误先跳过
                rs = preparedStatement.executeQuery();
            }
            int cnt = 0 ;
            float total = 0 ;
            int cntPoi = 0 ;
            float totalPoi = 0 ;
            while (rs.next()) {
                //判断地点是否相同
                if (StringUtils.isBlank(poiidLine) && StringUtils.isBlank(aoiidLine) ){
                    continue;
                }
                String aoiid = rs.getString(7);
                String poiid = rs.getString(18);
                if (!(StringUtils.equalsIgnoreCase(aoiidLine,aoiid) || StringUtils.equalsIgnoreCase(poiidLine,poiid))){
                    continue;
                }
                //地点相同，判断电流和温度
                String bmsBattcurrRmStr = rs.getString(8);
                if (StringUtils.isBlank(bmsBattcurrRmStr) || StringUtils.equalsIgnoreCase(bmsBattcurrRmStr,"-999999")){
                    bmsBattcurrRmStr="0";
                }
                String bmsRmcModuletempminStr = rs.getString(10);
                if (StringUtils.isBlank(bmsRmcModuletempminStr) || StringUtils.equalsIgnoreCase(bmsRmcModuletempminStr,"-999999")){
                    bmsRmcModuletempminStr="0";
                }
                float bmsBattcurrRm ;
                try {
                    bmsBattcurrRm = Float.parseFloat(bmsBattcurrRmStr);
                }catch (Exception e){
                    bmsBattcurrRm = 0f;
                }
                float bmsRmcModuletempmin;
                try {
                    bmsRmcModuletempmin = Float.parseFloat(bmsRmcModuletempminStr);
                }catch (Exception e){
                    bmsRmcModuletempmin = 0f;
                }
                float deltaCurr = Math.abs(Math.abs(curr) - Math.abs(bmsBattcurrRm));
                float deltaTemp = Math.abs(Math.abs(temp) - Math.abs(bmsRmcModuletempmin));
                if(deltaCurr<30 && deltaTemp < 5){
                    //电流相差30 温度相差5 那么就可以
                    float avgTime = rs.getFloat(6);
                    return avgTime;
                }
            }
            if (cnt==0){
                return null;
            }
            //判断地点是否相同
            if (cntPoi>0){
                return totalPoi / cntPoi;
            }
            float v = total / cnt;
            return v;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HBaseDataSource.ins().close(connection);
        }
        return null;
    }

    public Float queryMapId6(String condition,JSONObject line,float innerSoc) {
        //查一个曲线的开始点 一条曲线只需要查找开始点，之后根据开始点查找结束点，这样就是一条曲线
        Connection connection = null;
        try {
            String sql = selectStrV3.concat(condition);
            if (StringUtils.startsWithIgnoreCase(condition,DwsStrConstant.ins().selectStr)){
                sql = condition;
            }
            System.out.println(sql);
//            log.info(sql)
            connection = HBaseDataSource.ins().getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            Float curr = line.getFloat(currKey);
            Float temp = line.getFloat(acAmbKey2);
            String aoiidLine= line.getString(aoiIdKey);
            String poiidLine= line.getString(poiIdKey);
            String vin = line.getString(vinKey);
            String modelCode = line.getString(modelCodeKey);
            if (StringUtils.isBlank(vin)){
                vin = line.getString("deviceid");
            }
            if (StringUtils.isBlank(vin)){
                vin = line.getString("device_id");
            }
            ResultSet rs = null;
            try {
                rs = preparedStatement.executeQuery();
            }catch (Exception e){
                //第一次如果报错了，那么就重试一次，如果还是错误先跳过
                rs = preparedStatement.executeQuery();
            }
            int cnt = 0 ;
            float total = 0 ;
            int cntPoi = 0 ;
            float totalPoi = 0 ;
            int cntVin = 0 ;
            float totalVin = 0 ;
            int cntModelCode = 0 ;
            float totalModelCode = 0 ;
            while (rs.next()) {
                String deviceIdDb = rs.getString(1);
                String e = rs.getString(6);
                String modelCodeDb = rs.getString(7);
                if (StringUtils.isBlank(e)){continue;}
                float ef =0f;
                try {
                    ef = Float.parseFloat(e);
                }catch (Exception exception){
                    exception.printStackTrace();
                }
                //过滤异常值
                if (ef>2.1f){
                    continue;
                }
                //默认值
                cnt++;
                total += ef;
                //如果vin相同
                if (StringUtils.equalsIgnoreCase(vin,deviceIdDb)){
                    cntVin++;
                    totalVin += ef;
                }
                //如果model code相同
                if (StringUtils.equalsIgnoreCase(modelCode,modelCodeDb)){
                    cntModelCode++;
                    totalModelCode += ef;
                }
                //判断地点是否相同
                if (StringUtils.isBlank(poiidLine) && StringUtils.isBlank(aoiidLine) ){
                    continue;
                }
                String aoiid = rs.getString(9);
                String poiid = rs.getString(10);
                if (!(StringUtils.equalsIgnoreCase(aoiidLine,aoiid) || StringUtils.equalsIgnoreCase(poiidLine,poiid))){
                    continue;
                }
                //地点相同 且 vin 相同那么返回第一个即可
                if (StringUtils.equalsIgnoreCase(vin,deviceIdDb)){
                    return ef;
                }else {
                    //如果地点相同但vin不相同
                    cntPoi++;
                    totalPoi+=ef;
                }
            }
            //地点相同但vin不相同
            if (cntPoi > 0){
                return totalPoi / cntPoi;
            }
            //没有相同的地点但是vin有相同的
            if (cntVin > 0){
                return totalVin / cntVin;
            }
            //地点Vin都没有相同的但是有相同的model code
            if (cntModelCode > 0){
                return totalModelCode / cntModelCode;
            }
            //vin没有相同且地点没有相同的那就取一个默认值
            if (cnt > 0){
                return total / cnt;
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HBaseDataSource.ins().close(connection);
        }
        return null;
    }
}
